

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;
import java.util.Random;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;



import processing.core.*;

public class Graph extends PApplet implements TLModule{	
	//test
	int cycle; //counter
	int counter; //another counter
	
	boolean pictures = true;
	
	int screenSizeX = 700;
	int screenSizeY = 700;
	float radius = screenSizeX < screenSizeY ? 0.35f * screenSizeX : 0.35f * screenSizeY;
	PFont font = loadFont("ScalaSans-Caps-32.vlw");
	int userFontHeight = 16;
	int songFontHeight = 18;
	
	private boolean pause = false; //skips the drawing and all forces if true
	double minMoved = 0.3; //the minimum accumulative movement before the graph is paused

	Random rand = new Random(0);
	
	//maximum number of drawable objects handled:
	int maxConnections = 120;
	int maxCircles = 100;
	int maxSquares = 99;
	
	int numberOfConnections = 0;
	int numberOfCircles = 0;
	int numberOfSquares = 0;
	Circle[] circles = new Circle[maxCircles];
	GraphConnection[] graphConnections = new GraphConnection[maxConnections];
	Square[] squares = new Square[maxSquares];
	
	final float cPush = 0.2f; //constant for the pushing forces
	float vPush;			  //the for size and number compensated value of cPush
	final float cPull = 0.001f; //constant for the pulling forces
	final float cCent = 0.0005f; //constant for the centralizing forces
	
	//connection and database stuff
	Connection connection;
	boolean filtersChanged;
	boolean highlightChanged;
	//selection:
	List<Long> highlightPhrase;
	List<Long> highlightSong = new ArrayList<Long>();
	Hashtable<String, Object> highlightUser = new Hashtable<String, Object>();
	//filter:
	List<Long> phrase;
	List<Long> song;
	List<String> user;	
	Timestamp start;
	Timestamp end;
	
	//unnamed
	List<Square> allUsers = new ArrayList<Square>();
	List<Square> currentUsers = new ArrayList<Square>();
	Hashtable<String, Integer> currentSongs;
	Hashtable<String, Circle> allSongs = new Hashtable<String, Circle>();
	Hashtable<Long, Circle> songsById = new Hashtable<Long, Circle>();	
		
	/**
	 * necessary Processing method
	 * initializes everything
	 */
	public void setup() {
		
		size(screenSizeX, screenSizeY);
		
//		generateCircles();		
//		generateSquares();
//		generateConnections();
		
		connectToDatabase();
		retrieveData();
	}
	

	@SuppressWarnings({ "unchecked" })
	private void retrieveData() {		
		QueryRunner qr = new QueryRunner();
		ResultSetHandler rsh = new MapHandler();

		try {
			int limit = 250;
//			String sql = "select distinct song_title, from_user_id, emotion, tweet_id from lyrics, (select phrase_id, t1.* from phrase_tweet, (select tweet_id, from_user_id, emotion from tweet order by tweet_id desc limit 250) t1 where phrase_tweet.tweet_id = t1.tweet_id) t2 where lyrics.phrase_id = t2.phrase_id order by tweet_id desc;";
			String sql = "select song_id, t7.* from song join (select t3.*, overall_popularity, lastweek_popularity from (select song_title, overall_popularity, lastweek_popularity from (select song_title, sum(nr_tweets) lastweek_popularity from song_popularity where date between \"2010-05-01\" and \"2010-05-17\" group by song_title) t4 join (select song_title, sum(nr_tweets) overall_popularity from song_popularity group by song_title) t5 using (song_title)) t6 join (select t2.*, profile_image_url from user, (select distinct song_title, from_user_id, emotion, tweet_id from lyrics, (select phrase_id, t1.* from phrase_tweet, (select tweet_id, from_user_id, emotion from tweet order by tweet_id desc limit "+ limit + ") t1 where phrase_tweet.tweet_id = t1.tweet_id) t2 where lyrics.phrase_id = t2.phrase_id) t2 where user_id = t2.from_user_id) t3 using (song_title))t7 using (song_title) order by tweet_id desc";
			
			rsh = new MapListHandler();

			List<Map<String, Object>> result = (List<Map<String,Object>>)qr.query(connection, sql, rsh);
			System.out.println(result);
			
			convertData(result);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	} 

	
	private void convertData(List<Map<String, Object>> result) {
		Square current;
		float[] center = {screenSizeX / 2f,	screenSizeY / 2f};
		int color;
				
		float offset = 0f;
		for (int i = 0; i < result.size(); i++){
			//make/get song
			long tweets;
			long songID;			
			try{
				tweets = ((BigDecimal) result.get(i).get("overall_popularity")).longValue();
				songID = (Long) result.get(i).get("song_id");
			}
			catch(NumberFormatException nfe){
				System.err.println("Problems trying to parse sql data (Object -> int/long) in Graph:");
				nfe.printStackTrace();
				tweets = 0;
				songID = 0;
			}
			//TODO: set maxTweets at something good
			int maxTweets = 10000;
			tweets = (255 * tweets) / maxTweets;
			color = color(tweets, 0, 255 - (tweets));			
			String songName = (String) result.get(i).get("song_title");			
			Circle song = getSong(songName, color);
			songsById.put(songID, song);
			
			//make user
			String url = (String) result.get(i).get("profile_image_url"); 
			String userName = (String) result.get(i).get("from_user_id");
			
			current = new Square(this, center, radius, 0, userName, url, offset + 10, song);
			offset += 50;
			offset %= 150;
			
			//add and user
			allUsers.add(current);			
		}		
	}

	/**
	 * returns the requested song. if it doesn't already exists, it creates it.
	 * @param songName
	 * @param color
	 * @return
	 */
	private Circle getSong(String songName, int color) {
		if (!allSongs.containsKey(songName)){
			int defaultSize = 40;
			float[] center = {screenSizeX / 2f,	screenSizeY / 2f};
			Circle song = new Circle(this, center, defaultSize, songName, color);
			allSongs.put(songName, song);
		}
		return allSongs.get(songName);
	}


	/**
	 * makes a connection with the database
	 */
    private void connectToDatabase() {
    	connection = null;
    	try {
    		// Load the JDBC driver
    		String driverName = "org.gjt.mm.mysql.Driver"; // MySQL MM JDBC driver
    		Class.forName(driverName);

    		// Create a connection to the database
//    		String serverName = "djura.crabdance.com";
    		String serverName = "localhost";
    		String mydatabase = "infviz";
    		String url = "jdbc:mysql://" + serverName + "/" + mydatabase; // a JDBC url
    		String username = "root";
    		String password = "augmented";
    		connection = DriverManager.getConnection(url, username, password);
    	} catch (ClassNotFoundException e) {
    		System.out.println("Problem : " + e);
    		// Could not find the database driver
    	} catch (SQLException e) {
    		System.out.println("Problem : " + e);
    		// Could not connect to the database
    	}
    }
    
    /**
     * gets all the squares
     * @return
     */
    public Square[] getSquares(){    	
    	return this.squares;
    }
    
    /**
	 * generates random circles for testing purposes
	 */
	private void generateCircles() {
		float border = 0.3f * screenSizeX;
		float[] pos = new float[2];
		float size;
		int color;
		int emotion;
		String name;		
		for(int i=0; i < this.circles.length; i++){			
			pos[0] = border + (screenSizeX - 2 * border) * rand.nextFloat();
			pos[1] = border + (screenSizeY - 2 * border) * rand.nextFloat();
			size = rand.nextFloat() * 20 + 2;
			name = String.format("Song #%d" , i);
			emotion = rand.nextInt(256);
			color = color(emotion, 50, 255 - emotion);
//			addCircle(new Circle(this, pos, size, name, color));
		}	
	}

	/**
	 * adds a circle to the graph
	 * @param circle
	 */
	private void addCircle(Circle circle) {
		if (numberOfCircles < maxCircles){
			circles[numberOfCircles] = circle;
			numberOfCircles++;
		}		
	}	

	/**
	 * generates random graphConnections for testing purposes
	 */
	private void generateConnections() {
		int nc;
		int ns;
		float weight;
		int count = 0;
		while (numberOfConnections< maxConnections && count < 100){			
			nc = rand.nextInt(numberOfCircles);			
			ns = rand.nextInt(numberOfSquares);
//			if(n1 == n2){
//				count++;
//				continue;
//			}
//			else{				
				weight = rand.nextFloat() * 2 + 1;
				addGraphConnection(new GraphConnection(circles[nc], squares[ns], weight));
//			}
		}	
	}
	
	/**
	 * adds a connection to the graph
	 * @param graphConnection
	 */
	private void addGraphConnection(GraphConnection graphConnection) {
		if(numberOfConnections < maxConnections){
			graphConnections[numberOfConnections] = graphConnection;
			numberOfConnections++;
		}
	}
	
	/**
	 * rotate the users
	 */
	private void turnUsers(){		
		Square[] squares = getSquares();
		int n = squares.length;
		float angle = (float) (2 * Math.PI / n);
		for (int i = 0; i < numberOfSquares; i++){
			squares[i].turn(angle);
		}
	}
	
	/**
	 * generates a ring of squares	
	 */
	private void generateSquares(){
		float[] center = new float[2];
		float rotation;
		String name;
		float n = maxSquares;		
		for(int i = 0; i < n; i++){
			center[0] = 0.5f * screenSizeX;
			center[1] = 0.5f * screenSizeY;
			rotation = (float) ((i/n) * Math.PI * 2f + 3*Math.PI/2);
			name = String.format("user%3d", i);
//			this.addSquare(new Square(this, center, radius, rotation, name));			
		}
	}
	
	/**
	 * adds a square to the graph
	 * @param square
	 */
	private void addSquare(Square square) {
		if(numberOfSquares < maxSquares){
			squares[numberOfSquares] = square;
			numberOfSquares++;
		}
	}	
	
	/**
	 * gets called each frame
	 */
	public void draw() {
		//see whether there are new tweets that should be added
		if(checkForNewUser()){
			deleteUser();			
		}
		
		//add a user if there's a spot free
		if(currentUsers.size() < maxSquares && counter > 3){
			counter = 0;
			turnUsers();
			addNextUser();
		}
		counter++;
		
		//put the songs and users into the squares and circles and connections arrays
		fillArrays();

		//do calculations
		double moved = 0;
		for(int i = 0; i < 3; i++){
			moved = exertForces();				
		}
		//skip calculating and moving next loop if total distance moved is small
		if (moved < minMoved)
			pause = true;

		//draw background
		background(255);
		fill(180);
		noStroke();
		ellipse(screenSizeX/2f, screenSizeY/2f, 2*radius, 2*radius);

		//draw the graph
		drawConnections();
		drawCircles();
		drawSquares();		
	}

	/**
	 * add the first user from the all users list and put it at the back
	 * of the currentUsers
	 */
	private void addNextUser() {
		// TODO Auto-generated method stub
		currentUsers.add(allUsers.get(0));
		allUsers.remove(0);
	}

	/**
	 * delete the first current user
	 */
	private void deleteUser() {
		// TODO Auto-generated method stub
		currentUsers.remove(0);
	}

	/**
	 * check whether there is a new user
	 * at this point, this is done randomly as long as there is some user available
	 * @return true when a new tweet is available, false otherwise
	 */
	private boolean checkForNewUser() {
		if (rand.nextInt(100) < 1 && allUsers.size() > 0)
			return true;
		else 
			return false;
	}


	/**
	 * Fills the 3 arrays, squares, songs, graphConnections with objects from the lists.
	 * a bit weird to put all objects from one data structure to another but this way
	 * i can implement the sql stuff without changing the rest of the program too much.	 * 
	 */
	private void fillArrays() {
		//reinitiate the arrays
		squares = new Square[maxSquares];
		numberOfSquares = 0;
		circles = new Circle[maxCircles];
		numberOfCircles = 0;
		graphConnections = new GraphConnection[maxConnections];
		numberOfConnections = 0;
		
		//fill arrays 
		float weight = 1f;
		Square square;
		currentSongs = new Hashtable<String, Integer>();
		for(int i = 0; i < currentUsers.size(); i++){
			square = currentUsers.get(i);			
			addSquare(square);
			//make it selected or not
			if(highlightUser.containsKey(square.userName))
				square.highlight = true;
			else
				square.highlight = false;
			
			Circle song = square.getSong();			
			addGraphConnection(new GraphConnection(song, square, weight));			
			if (!currentSongs.containsKey(song.name)){
				currentSongs.put(song.name, 1);					
				song.setSize(1);
				addCircle(song);				
			}
			else{
				int n = currentSongs.get(song.name) + 1;
				currentSongs.put(song.name, n);
				song.setSize(n);				
			}
		}		
	}


	/**
	 * calculate spring forces and pass them to the nodes
	 * @return the total distance moved by all nodes
	 */
	private double exertForces() {
		pullForces();	
		pushForces();
		centralizingForces();
		return moveNodes();
	}	

	/**
	 * moves all nodes one step (by their own calculated direction)
	 * @return the total distance moved by all nodes
	 */
	private double moveNodes() {
		Node[] nodes = getNodes();
		Double moved = 0.0;
		for(Node node : nodes){
			node.dampen(); //makes sure things will come to rest
			moved += node.move();
		}
		return moved;
	}

	/**
	 * calculate all pushing forces between nodes
	 */
	private void pushForces() {
		//adapt vPush to prevent unpredictable behavior
		float radComp = (radius/800.0f);
		float circlesComp = (100.0f/numberOfCircles);
		float squareComp = (100.0f/numberOfSquares);
		vPush = radComp * circlesComp * squareComp * cPush;
		
		//gather all nodes in one array
		Node[] nodes = getNodes();			
		
		//let each node interact with the others
		Node current;
		Node other;
		float[] difference = new float[2];
		float[] force = new float[2];
		float constant;
		float distance;
		
		for(int i = 0; i < nodes.length; i++){
			current = nodes[i];			
			for(int j = 0; j < nodes.length; j++){
				if(j == i)
					continue;
				else{
					other = nodes[j];					
					difference = calcDifference(other.position, current.position);
					distance = calcDistance(other.position, current.position);
					if(distance == 0.0f)
						constant = 0.0f;
					else
						constant = vPush * current.mass * other.mass / (distance * distance * distance);
					force[0] = constant * difference[0];
					force[1] = constant * difference[1];
					current.exertForce(force);
				}
			}//inner for
		}//outer for
	}

	/**
	 * gathers all nodes from circles and squares
	 * @return
	 */
	private Node[] getNodes() {
		Node[] nodes = new Node[numberOfCircles + numberOfSquares];
		for(int i = 0; i < numberOfCircles; i++){
			nodes[i] = circles[i];			
		}
		for(int i = 0; i < numberOfSquares; i++){
			nodes[i + numberOfCircles] = squares[i];			
		}
		return nodes;
	}

	/**
	 * calculate pulling forces for all graphConnections
	 */
	private void pullForces() {
		GraphConnection graphConnection;
		Node node1;
		Node node2;
		float[] difference;
		float[] force = new float[2];
		float[] minForce = new float[2];
		float constant;		
		for(int i = 0; i < numberOfConnections; i++){
			graphConnection = this.graphConnections[i];
			node1 = graphConnection.nodes[0];
			node2 = graphConnection.nodes[1];
			difference = calcDifference(node1.position, node2.position);
			constant = cPull * graphConnection.weight;
			force[0] = constant * difference[0];
			force[1] = constant * difference[1];
			minForce[0] = -force[0];
			minForce[1] = -force[1];
			node1.exertForce(force);
			node2.exertForce(minForce);			
		}		
	}
	
	/**
	 * Exerts a force on all nodes out of a certain radius of the center that
	 * pushes them back inwards.
	 */
	private void centralizingForces(){
		float innerRadius = 0.8f * radius;
		float center[] = {(float)screenSizeX / 2f, (float)screenSizeY / 2f};
		float force[] = new float[2];
		float difference[] = new float[2];
		float distance;
		float magnitude;
		float maxm = 10000f;
		for(Node node: this.getNodes()){
			distance = calcDistance(center, node.position);
			if (distance > innerRadius){
				difference = calcDifference(node.position, center);
				force[0] = cCent * (distance - innerRadius) * difference[0];
				force[1] = cCent * (distance - innerRadius) * difference[1];
				
				magnitude = (float) Math.sqrt(Math.pow(force[0], 2)+
						Math.pow(force[1], 2));
				
				//if it all goes out of hand. return the node to the center
				if(magnitude > maxm){
					node.position = center.clone();
				}

				node.exertForce(force);
			}
		}
	}

	/**
	 * calculates the difference vector between the two positions
	 * @param position1
	 * @param position2
	 * @return
	 */
	private float[] calcDifference(float[] position1, float[] position2) {
		float[] result = new float[2];
		result[0] = position2[0] - position1[0];
		result[1] = position2[1] - position1[1];
		return result;
	}
	
	/**
	 * calc the distance between two positions
	 * @param position1
	 * @param position2
	 * @return
	 */
	private float calcDistance(float[] position1, float[] position2) {
		float result;
		result = (float) Math.sqrt(Math.pow((position2[0] - position1[0]),2) 
				+ Math.pow((position2[1] - position1[1]),2));		
		return result;
	}

	/**
	 * draw all graphConnections in the graphConnections field
	 */
	private void drawConnections() {
		for(int i=0; i < numberOfConnections; i++){
			drawGraphConnection(graphConnections[i]);
		}	
	}
	
	/**
	 * draw one connection
	 * @param graphConnection
	 */
	private void drawGraphConnection(GraphConnection graphConnection) {
		Node node1 = graphConnection.nodes[0];
		Node node2 = graphConnection.nodes[1];
		
		stroke(255);
		strokeWeight(1);
		this.line(node1.position[0], node1.position[1], 
				node2.position[0], node2.position[1]);
		
		stroke(node1.color);
		strokeWeight(1);
		this.line(node1.position[0], node1.position[1], 
				node2.position[0], node2.position[1]);
	}

	/**
	 * draw all circles in the circles field
	 */
	private void drawCircles() {
		for(int i=0; i < numberOfCircles; i++){
			drawCircle(circles[i]);
		}
		Circle current;	
//		System.out.println("Size: " + highlightSong.size());
		for(int i=0; i < highlightSong.size(); i++){
			current = songsById.get(highlightSong.get(i));
			if (current != null && currentSongs.containsKey(current.name))
				drawSelectedCircle(current);
		}
			
	}
	
	/**
	 * draw one selected circle
	 * @param c the circle
	 */
	private void drawSelectedCircle(Circle c){
		stroke(0);	
		textFont(font, songFontHeight);
		pushMatrix();		
		translate(c.position[0],c.position[1]);
		
		//outer vinyl
		stroke(255);
		fill(100);
		ellipse(0f, 0f, 2*c.radius, 2*c.radius);
		
		//inner vinyl
		fill(c.color);
		ellipse(0f, 0f, 0.8f* c.radius, 0.8f* c.radius);
		
		//hole in vinyl
		stroke(200);
		fill(180);
		ellipse(0f, 0f, 0.05f*c.radius, 0.05f*c.radius);
		
		//text
		fill(255);
		text(c.name, 2-c.radius, 2*songFontHeight/3);

		popMatrix();
	}
	
	/**
	 * draw one circle
	 * @param c the circle
	 */
	private void drawCircle(Circle c){
		stroke(0);	
		textFont(font, songFontHeight);
		pushMatrix();		
		translate(c.position[0],c.position[1]);
		
		//outer vinyl
		fill(0);
		ellipse(0f, 0f, 2*c.radius, 2*c.radius);
		
		//inner vinyl
		fill(c.color);
		ellipse(0f, 0f, 0.8f* c.radius, 0.8f* c.radius);
		
		//hole in vinyl
		stroke(200);
		fill(180);
		ellipse(0f, 0f, 0.05f*c.radius, 0.05f*c.radius);
		
		float bigSongRadius = 15f;
		if (c.radius > bigSongRadius){
			fill(255);
			text(c.name, 2-c.radius, 2*songFontHeight/3);
		}
		popMatrix();
	}

	/**
	 * draw all squares in the squares field
	 */
	private void drawSquares() {
		for(int i=0; i < numberOfSquares; i++){
			drawSquare(squares[i]);
		}		
	}
	
	/**
	 * draw one square
	 * @param c the square
	 */
	private void drawSquare(Square s){
		stroke(0);
		fill(0);
		textFont(font, userFontHeight);
		pushMatrix();

		translate(s.position[0],s.position[1]);
		rotate(s.rotation);

		rect(-0.5f * s.width, -0.5f * s.heigth, s.width, s.heigth);
		if(pictures){
			if(s.image != null){
				scale(0.6f);
				line(0,0,s.imageOffset,0);
				image(s.image, s.imageOffset, -24);
			}
		}
		else{
			if (s.highlight){
				fill(255);
				text(s.userName, 10, userFontHeight/3);
			}
			else{			
				text(s.userName, 10, userFontHeight/3);	
			}
		}
		
		popMatrix();
	}

	@Override
	public void highlightPhrase(List<Long> phraseId) {
		this.highlightPhrase = phraseId;
		highlightChanged = true;
		pause = false;		
	}

	@Override
	public void highlightSong(List<Long> songId) {
		this.highlightSong = songId;
		highlightChanged = true;
		pause = false;		
	}

	@Override
	public void highlightUser(List<String> users) {
		for (int i = 0; i < users.size(); i++){
			highlightUser.put(users.get(i), null);
		}
		this.user = users;
				
		highlightChanged = true;
		pause = false;		
	}

	@Override
	public void reset() {
		resetUser();
		resetSong();
		resetPhrase();
		resetTime();
		
		filtersChanged = true;		
		pause = false;		
	}

	@Override
	public void resetPhrase() {
		List<Long> phrases = new ArrayList();
		highlightPhrase(phrases);
		setPhrase(phrases);
				
		filtersChanged = true;		
		pause = false;
	}

	@Override
	public void resetSong() {
		List<Long> songs = new ArrayList();
		highlightSong(songs);
		setSong(songs);

		filtersChanged = true;		
		pause = false;
	}

	@Override
	public void resetTime() {
		setTimeSpan(null, null);
		
		filtersChanged = true;		
		pause = false;
	}

	@Override
	public void resetUser() {
		List<String> users = new ArrayList();
		highlightUser(users);
		setUser(users);

		filtersChanged = true;		
		pause = false;
	}

	@Override
	public void setPhrase(List<Long> phraseId) {
		this.phrase = phraseId;
		
		filtersChanged = true;		
		pause = false;
	}

	@Override
	public void setSong(List<Long> songId) {
		this.song = songId;
		
		filtersChanged = true;		
		pause = false;
	}

	@Override
	public void setTimeSpan(Timestamp start, Timestamp end) {
		this.start = start;
		this.end = end;
		
		filtersChanged = true;		
		pause = false;
	}

	@Override
	public void setUser(List<String> users) {
		this.user = users;
		
		filtersChanged = true;		
		pause = false;
	}


	@Override
	public void resetBillBoard(String chart_name) {
		// TODO Auto-generated method stub
		
	}


	@Override
	public void setBillBoard(String chart_name) {
		// TODO Auto-generated method stub
		
	}
}